PostgreSQL 配置与管理
Table of Contents
1. 配置文件管理
FreeBSD 系统使用 pkg 安装数据库的话，初始化数据库生成 PGDATA 目录后，配置文件在 PGDATA 目录中。
RedHat 系系统中配置文件的路径是 /var/lib/pgsql/data (PGDATA 目录)，它的配置文件在 PGDATA 目录中，初始化之后才会出现。
FreeBSD 和 RedHat 系系统中配置文件的路径都在 PostgreSQL 官方文档中所有说明。即，先在某个目录中初始化数据库 (生成数据库文件)，然后使用这个目录中包含的数据库配置文件。
FreeBSD 系统中，用户需要自行在 /etc/rc.conf 中配置 PostgreSQL 的默认参数: 是否在开机时自动启动，PGDATA 的位置、PostgreSQL 以什么样的方式关闭等等。但是，它只能配置一个 PostgreSQL cluster 的参数。
RedHat 系系统中，用于管理 postgresql 的 systemd .service 文件中将 PGDATA 目录写死了: Environment=PGDATA=/var/lib/pgsql/data 。这就意味着，如果用户不做一些修改，只使用系统默认提供的工具的话，也只能管理一个 PostgreSQL cluster。
而在 Debian 系系统中，用户可以安装不同版本的数据库，并且通过 postgresql-common 包提供的工具同时管理不同版本数据库的多个 cluster。
安装 PostgreSQL 时，Debian 系系统默认会在 /etc/postgresql/<version>/<cluster-name> 路径下生成配置文件，并将 /var/lib/postgresql/<version>/<cluster-name> 路径作为 PGDATA 目录。比如，安装了 PostgreSQL 15 和 16 就会有 /etc/postgresql/15 和 /etc/postgresql/16 两个配置目录，以及 /var/lib/postgresql/15/main 和 /var/lib/postgresql/16/main 两个 PGDATA 目录。
其中: 这两个路径中的 15,16 是 PostgreSQL 的版本号， main 是 cluster 的名称。<cluster-name> 的默认值是 main 。
安装 PostgreSQL 时，PostgreSQL 依赖了一个叫做 postgresql-common 的包，这个包里面包含了一系列 perl 脚本用来管理多个 PostgreSQL cluster，这些脚本的本质是对于 pg_ctl 命令的包装。
注: postgresql-common 这个包，包含了管理 PostgreSQL 所需的所有组件，包括 systemd 文件、perl 脚本、man page 等等。
此外，需要注意的一点是 /lib/systemd/system/postgresql.service 这个文件只是写作 .service 实际用作 .target 对象。只是因为 .target 不能实现重启服务的功能，所以才把它写成了 .service。真正包含 PostgreSQL 配置的 .service 文件是 /lib/systemd/system/[email protected]。[email protected] 的 [Unit] 字段中也写明了 PartOf=postgresql.service 。
用户可以注意到，在 Debian 系系统中，只要执行 systemctl enable/restart/stop postgresql 就可以完成对 PostgreSQL cluster 的控制，而不必直接调用 [email protected] 文件。这是因为，系统在启动时，/usr/lib/systemd/system-generators/postgresql-generator 这个 systemd.generator 就已经根据配置文件的 /etc/postgresql/<version>/<cluster-name> 目录结构，为多个 cluster 生成了对应的 .service 文件，这些 .service 文件被放在 /run/systemd/generator/postgresql.service.wants 目录下。
比如，用户安装了 PostgreSQL 15 和 16 两个版本，就会有 /run/systemd/generator/postgresql.service.wants/[email protected] 和 /run/systemd/generator/postgresql.service.wants/[email protected] 两个 .service 文件。其中，15,16 是版本号，main 是 cluster 的名称。这两个配置都是指向 /lib/systemd/system/[email protected] 的符号链接。
systemctl 命令对于 postgresql.service 的操作实际上同时作用在 [email protected] 和 [email protected] 两个文件上。这也意味着如果用户执行 systemctl stop postgresql 后，所有的 PostgreSQL cluster 都会停止。
如果用户有多个 cluster，则对于某一个 PostgreSQL cluster 的操作就需要通过 postgresql-common 包中的 perl 脚本来完成:
- /usr/bin/pg_backupcluster
- /usr/bin/pg_buildext
- /usr/bin/pg_config
- /usr/bin/pg_conftool
- /usr/bin/pg_createcluster
- /usr/bin/pg_ctlcluster
- /usr/bin/pg_dropcluster
- /usr/bin/pg_lsclusters
- /usr/bin/pg_renamecluster
- /usr/bin/pg_restorecluster
- /usr/bin/pg_upgradecluster
- /usr/bin/pg_virtualenv
当然，用户也可以通过手动操作 /etc/postgresql 目录下的配置文件并在对应位置初始化数据库目录来手动创建 cluster，但这种情况下 /run/systemd/generator/postgresql.service.wants 中拥有 postgresql@<version>-<clustre-name>.service 名称的，指向 /lib/systemd/system/[email protected] 的符号链接并不会被自动生成。
此时可以 ln -s /lib/systemd/system/[email protected] /run/systemd/generator/postgresql.service.wants/postgresql@<version>-<clustre-name>.service 手动创建对应的 .service 文件。
或者，使用 systemctl daemon-reload 让 systemd 重新执行 /usr/lib/systemd/system-generators 目录下存放的用于生成 .service 文件的所有脚本，更新所有需要动态生成的 .service 文件，从而重新生成 /run/systemd/generator/postgresql.service.wants 目录下所需要的文件。
2. 通用的配置
2.1. pg_hba.conf 中的配置
有时，在切换到数据库中的其他用户 other_u 时会报错: Peer authentication failed for user
psql 的连接基于 Unix Socket 建立，而在配置文件中，它被配置为使用 peer authentication 的方式来做用户认证 (local all all peer)。这意味着在 psql 切换到 other_u 用户时，other_u 用户必须真实存在于在当前的操作系统中，否则无法通过 peer 认证。
要解决这个问题，可以:
- 只使用远程连接来使用 other_u 用户
- 在操作系统中创建 other_u 用户
- 将 peer 认证改为密码认证
- 注释 local all all peer
- 添加 local all all scram-sha256(debian)/scram-sha-256(RedHat)
2.2. postgresql.conf 中的配置
- shared_buffers
默认 128M。值为 -1 时，它取总内存的 25%。这个值在数据库专用服务器上也适用。
如果系统内还跑了其他的应用，那么这个值可以用来设计内存的划分。比如，在 32G 内存的系统上，shared_buffers 设置为 1G，那么数据库需要的总内存可能是 1G/25%=4G，其余的内存可以分配给其他程序。
- effective_cache_size
默认 4G。值为 -1 时，它取总内存的 75%。更改此设置不需要重新启动数据库 (发送 HUP 信号即可)。
它的值应设置为在考虑操作系统本身和其他应用程序使用的内存后，可用于磁盘缓存的内存大小。这个值由 PG 的查询计划器使用，用来指导查询应该使用索引还是扫全表，并不是在给 PG 分配内存。如果设置得太低，索引可能无法按预期方式用于查询操作。
取总内存的 50% 是一个正常、保守的值；取总内存的 75% 依旧合理。如果系统内还跑了其他的程序，可以参考 shared_buffers 的值，将 effective_cache_size 的值设为 shared_buffers 的三倍；当 shared_buffers*3<effective_cache_size 时可以保持默认值 4G。
3. 针对 ZFS/btrfs 文件系统的配置
3.1. postgresql.conf 中的配置
- full_page_writes
默认值 on，推荐值 off。
在使用这个功能时，PG 会在 checkpoint 之后，第一次修改 page 时将 page 的原始内容写入 WAL 中。如果刷脏数据时断电，导致旧数据被破坏而新数据没有记录完成，这个 page 就被破坏了。此时就可以使用 WAL 中保存到被破坏前的 page 来覆盖整个 page 来恢复其原始值。
然而，ZFS/btrfs 的事务特性不存在这种数据更新了一半的情况，要么更新成功，要么失败回滚。所以这个功能不必打开。
- wal_init_zero
默认值 on，推荐值 off。
使用这个功能时，新创建的 WAL 文件会被零填充。在传统文件系统上，这可以确保写入 WAL 记录之前预先分配空间从而提高性能。但是 ZFS/btrfs 的压缩功能使得这个文件并没有被预先分配空间 (但这个文件记录了 0 值的数量，并且在读这个文件时可以像预先分配了空间那样，读出所有的 0 值)。
所以在开启了压缩功能的 ZFS/btrfs 上 PG 并不会从这个行为中受益，反而这个填充行为有些浪费资源。设置为 off，则在使用 WAL 文件时仅写入应被写入的数据，以便其具有预期大小。
- wal_sync_method
默认值 fsync，在 ZFS/btrfs 上不推荐使用 datasync。
搜索引擎提供的很多教程都推荐使用 datasync，但它除了在性能上有一点点优势之外，安全性远不如 fsync。
fsync 在元数据和数据写入完成前不会结束，这保证了文件的元数据和数据被一起更新。而 fdatasync 的元数据写入和数据写入是分开的，它只保证数据已经写入完成，这意味着元数据可能丢失。举例来说，文件的大小 (st_size) 如果变大，需要立即操作元数据，否则 OS 崩溃时，即使文件的数据部分已同步，但元数据没有同步，那么依然读不到增加的内容。
在 PG 中，不论 wal_init_zero 是否开启，WAL 文件在创建时都不会被预先分配空间。这意味着，每次向 WAL 写入数据后，WAL 文件元数据总是需要被更新 (需要记录新分配的空间的地址)。而 fdatasync 存在丢失元数据的可能，所以推荐保持原始值。
- wal_recycle
默认值 on，推荐值 off。
使用这个功能时，PG 会通过重命名来重复使用 WAL 文件，从而避免删除文件和创建文件带来的开销。但是 ZFS/btrfs 的 COW 特性让使用全新文件的开销更小，所以这个功能应该关闭。
3.2. 数据库内部的配置
- 不使用 PG checksums 功能
ZFS/btrfs 自带了数据块校验，不必在 PG 里面重复校验。
- 在建表时不使用 compression
ZFS/btrfs 由压缩功能，PG 不必在 table 层面重新压缩一遍。
4. 数据文件迁移
切换到 postgres 用户后执行 psql -c "SHOW data_directory;" 即可看到数据文件所在目录的路径。Debian 系系统中通常是 /var/lib/postgresql/<pg-version> 目录；RedHat 系系统中通常是 /var/lib/pgsql/data 目录 (这个目录也是 postgres 用户的家目录)。
迁移该目录下的文件后，Debian 系系统中要修改 /etc/postgresql/11/main/postgresql.conf 内 data_directory 的值使之指向迁移后的路径。
而在 RedHat 系系统中需要执行 sudo systemctl edit postgresql.service 写下以下内容:
[Service] Environment=PGDATA=<new-pg-data-path> # for example: # Environment=PGDATA=/storage0/apps/pg15data
内容会被储存在 /etc/systemd/system/postgresql.service.d/override.conf 文件中。
重启数据库后，重新执行 psql -c "SHOW data_directory;" 检查新路径是否生效。